﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Reflection;
using System.Web;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace Nova_Common
{
    public class ExcelHelper : IDisposable
    {
        private string fileName = null;
        private IWorkbook workbook = null;
        private FileStream fs = null;
        private bool disposed;
        public ExcelHelper(string fileName)
        {
            this.fileName = fileName;
            disposed = false;
        }
        public void setHeadCellStyle(IWorkbook workbook, ICell cell)
        {
            HSSFCellStyle hSSFCellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
            HSSFFont hSSFFont = (HSSFFont)workbook.CreateFont();
            hSSFFont.FontHeight = 210.0;
            hSSFFont.FontName = "微软雅黑";
            hSSFFont.Boldweight = short.MaxValue;
            hSSFFont.Color = 8;
            hSSFCellStyle.SetFont(hSSFFont);
            hSSFCellStyle.FillBackgroundColor = 11;
            hSSFCellStyle.VerticalAlignment = VerticalAlignment.Center;
            hSSFCellStyle.Alignment = HorizontalAlignment.Center;
            cell.CellStyle = hSSFCellStyle;
        }
        public void setCellStyle(IWorkbook workbook, ICell cell)
        {
            HSSFCellStyle hSSFCellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
            HSSFFont hSSFFont = (HSSFFont)workbook.CreateFont();
            hSSFFont.FontName = "微软雅黑";
            hSSFCellStyle.SetFont(hSSFFont);
            hSSFCellStyle.VerticalAlignment = VerticalAlignment.Center;
            hSSFCellStyle.Alignment = HorizontalAlignment.Center;
            cell.CellStyle = hSSFCellStyle;
        }
        public void setCellStyle2(HSSFCellStyle hSSFCellStyle, ICell cell)
        {
            HSSFFont hSSFFont = (HSSFFont)workbook.CreateFont();
            hSSFFont.FontName = "微软雅黑";
            hSSFCellStyle.SetFont(hSSFFont);
            hSSFCellStyle.VerticalAlignment = VerticalAlignment.Center;
            hSSFCellStyle.Alignment = HorizontalAlignment.Center;
            cell.CellStyle = hSSFCellStyle;
        }
        public ISheet SetColoumnCellWidht(ISheet sheet)
        {
            sheet.SetColumnWidth(0, 4120);
            sheet.SetColumnWidth(1, 4120);
            sheet.SetColumnWidth(2, 6120);
            sheet.SetColumnWidth(3, 5120);
            sheet.SetColumnWidth(4, 4120);
            sheet.SetColumnWidth(5, 5120);
            sheet.SetColumnWidth(6, 6120);
            sheet.SetColumnWidth(7, 4120);
            sheet.SetColumnWidth(8, 6120);
            sheet.SetColumnWidth(9, 4120);
            sheet.SetColumnWidth(11, 5120);
            return sheet;
        }
        public MemoryStream DataTableToExcel(DataTable data, string sheetName, Dictionary<string, string> headCell, bool isColumnWritten)
        {
            int num = 0;
            int num2 = 0;
            int num3 = 0;
            ISheet sheet = null;
            MemoryStream memoryStream = null;
            memoryStream = new MemoryStream();
            if (fileName.IndexOf(".xlsx") > 0)
            {
                workbook = new XSSFWorkbook();
            }
            else if (fileName.IndexOf(".xls") > 0)
            {
                workbook = new HSSFWorkbook();
            }

            try
            {
                if (workbook != null)
                {
                    sheet = workbook.CreateSheet(sheetName);
                    sheet = SetColoumnCellWidht(sheet);
                    if (isColumnWritten)
                    {
                        IRow row = sheet.CreateRow(0);
                        row.Height = 30 * 20;
                        for (num2 = 0; num2 < data.Columns.Count; num2++)
                        {
                            ICell cell = row.CreateCell(num2);
                            setHeadCellStyle(workbook, cell);
                            cell.SetCellValue(headCell[data.Columns[num2].ColumnName]);
                        }

                        num3 = 1;
                    }
                    else
                    {
                        num3 = 0;
                    }

                    string posValue = string.Empty;
                    string trayValue = string.Empty;
                    for (num = 0; num < data.Rows.Count; num++)
                    {
                        IRow row2 = sheet.CreateRow(num3);
                        row2.Height = 400;
                        HSSFCellStyle hSSFCellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                        for (num2 = 0; num2 < data.Columns.Count; num2++)
                        {
                            ICell cell2 = row2.CreateCell(num2);
                            // setCellStyle(workbook, cell2);
                            setCellStyle2(hSSFCellStyle, cell2);
                            cell2.SetCellValue(data.Rows[num][num2].ToString());
                        }

                        if (posValue == data.Rows[num][0].ToString())
                        {
                            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(num, num + 1, 0, 0));
                        }
                        posValue = data.Rows[num][0].ToString();
                        if (trayValue == data.Rows[num][1].ToString())
                        {
                            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(num, num + 1, 1, 1));
                        }
                        trayValue = data.Rows[num][1].ToString();
                        num3++;
                    }

                    workbook.Write(memoryStream);
                    HttpContext.Current.Response.BinaryWrite(memoryStream.ToArray());
                    return memoryStream;
                }

                return null;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
                return null;
            }
        }

        public void Dispose()
        {
            Dispose(disposing: true);
            GC.SuppressFinalize(this);
        }
        protected virtual void Dispose(bool disposing)
        {
            if (!disposed)
            {
                if (disposing && fs != null)
                {
                    fs.Close();
                }

                fs = null;
                disposed = true;
            }
        }
    }
}
